package com.yellowpage.omc.datacollect.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.LinkedMap;

import com.yellowpage.ndc.dao.ManageRegionDao;
import com.yellowpage.omc.datacollect.dao.DataCollectDao;
import com.yellowpage.omc.datacollect.data.DataCollect;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;

public class DataCollectServiceImpl extends BaseServiceImpl implements
		DataCollectService {
	private DataCollectDao dataCollectDao;
	private ManageRegionDao manageRegionDao;

	public void setDataCollectDao(DataCollectDao dataCollectDao) {
		this.dataCollectDao = dataCollectDao;
	}

	/* Smooth generate Method */
	public DataCollect getDataCollect(Long id) {
		return dataCollectDao.get(id);
	}

	public List<DataCollect> getAllDataCollect() {
		return dataCollectDao.getAll();
	}

	public List<Map> queryDataCollectByPage(Map queryMap, PageRender pageRender) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.DATA_COLLECT_ID , a.DATA_COLLECT_BATCH_ID , a.SEQ , a.LINK , a.TEL_TYPE , a.TEL , a.CITY , a.REGION_CODE, a.CLASS01 , a.CLASS02 , a.ADDR , a.FAX , a.TEL02 , " +
						" date_format(a.CREATE_TIME,'%Y-%m-%d %T') as CREATE_TIME, a.LAST_DATA_VERIFY_ID , date_format(a.LAST_VERIFY_TIME,'%Y-%m-%d %T') as LAST_VERIFY_TIME , " +						
						" (select c.CUST_ID  from db_ndc.LISTING l, db_ndc.CUSTOMER c, db_ndc.ADDRESS ADDR " +
						"  WHERE l.CUST_ID = c.CUST_ID and l.ADDR_ID = ADDR.ADDR_ID  and l.LIST_ID = a.LIST_ID   ) as NDC_CUST_ID," +
						" (select c.NAME1    from db_ndc.LISTING l, db_ndc.CUSTOMER c, db_ndc.ADDRESS ADDR " +
						"  WHERE l.CUST_ID = c.CUST_ID and l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID  ) as NDC_CUST_NAME," +
						" (select ADDR.ADDR  from db_ndc.LISTING l,db_ndc.ADDRESS ADDR  " +
						"  WHERE l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID  ) as NDC_ADDRESS, " +						
						" a.NOTE , a.NAME , a.ICON , a.INFO , b.BATCH_NUM ,   " +
						" (select display_name from CA_USER where user_id = a.LAST_VERIFY_USER) as LAST_UPDATE_USER, " +
						"        (select odt.name from OMC_DIC_TABLE odt where odt.dic_name = 'DATA_COLLECT_OPT' and odt.value = a.OPT_TYPE) as OPT_TYPE , " +
						" (select odt.name from OMC_DIC_TABLE odt where odt.dic_name = 'DATA_COLLECT_STATUS' and odt.value = a.STATUS) as STATUS " + 
						" FROM OMC_DATA_COLLECT a,OMC_DATA_COLLECT_BATCH b " +
						" WHERE 1 = 1 and a.DATA_COLLECT_BATCH_ID = b.DATA_COLLECT_BATCH_ID ")
				// .addCondition(" and DATA_COLLECT_ID = :dataCollectId ")
				// .addCondition(" and DATA_COLLECT_BATCH_ID = :dataCollectBatchId ")
				.addCondition(" and BATCH_NUM = :batchNum ")
				.addCondition(" and SEQ = :seq ")
				.addCondition(" and LINK = :link ")
				.addCondition(" and TEL_TYPE = :telType ")
				.addCondition(" and TEL = :tel ")
				.addCondition(" and CITY = :city ")
				.addCondition(" and CLASS01 = :class01 ")
				.addCondition(" and CLASS02 = :class02 ")
				.addCondition(" and ADDR = :addr ")
				.addCondition(" and FAX = :fax ")
				.addCondition(" and TEL02 = :tel02 ")
				.addCondition(
						" and CREATE_TIME = to_date(:createTime ,'yyyy-MM-dd') ")
				// .addCondition(" and LAST_DATA_VERIFY_ID = :lastDataVerifyId ")
				.addCondition(
						" and LAST_VERIFY_TIME = to_date(:lastVerifyTime ,'yyyy-MM-dd') ")
				.addCondition(" and c.DISPLAY_NAME = :lastVerifyUser ")
				.addCondition(" and NOTE = :note ").addCondition(
						" and a.NAME like :name ")
				.addCondition(" and ICON = :icon ").addCondition(
						" and INFO = :info ").addCondition(
						" and a.STATUS = :status ");

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}

	public List<Map> toEditDataCollectById(Map queryMap) {
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						" SELECT a.DATA_COLLECT_ID , a.DATA_COLLECT_BATCH_ID , a.SEQ , a.LINK , a.TEL_TYPE , a.TEL , a.REGION_CODE, a.CITY , a.CLASS01 , a.CLASS02 , a.ADDR , a.FAX , a.TEL02 , " +
						"       (select c.CUST_ID  from db_ndc.LISTING l, db_ndc.CUSTOMER c, db_ndc.ADDRESS ADDR " +
						"         WHERE l.CUST_ID = c.CUST_ID and l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID   ) as NDC_CUST_ID," +
						"       (select c.NAME1    from db_ndc.LISTING l, db_ndc.CUSTOMER c, db_ndc.ADDRESS ADDR " +
						"         WHERE l.CUST_ID = c.CUST_ID and l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID ) as NDC_CUST_NAME," +
						"       (select ADDR.ADDR  from db_ndc.LISTING l,db_ndc.ADDRESS ADDR  " +
						"         WHERE l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID  ) as NDC_ADDRESS," +
						"        a.CREATE_TIME , a.LAST_DATA_VERIFY_ID , a.LAST_VERIFY_TIME , a.LAST_VERIFY_USER , a.NOTE , a.NAME , a.ICON , a.INFO , a.STATUS , b.BATCH_NUM  " +
						"   FROM OMC_DATA_COLLECT a,OMC_DATA_COLLECT_BATCH b WHERE 1 = 1 and a.DATA_COLLECT_BATCH_ID = b.DATA_COLLECT_BATCH_ID ")
				.addCondition(" and DATA_COLLECT_ID = :id ");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				queryMap);

		return resultList;
	}
	
	public Map getStatusMap(){
    	Map dataSourceMap = new LinkedMap();
    	
    	QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
		" select NAME, VALUE from OMC_DIC_TABLE where DIC_NAME = 'DATA_COLLECT_STATUS' ")
					    ; 	
         
		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), new HashMap());
				
		
		for(int i = 0; i < resultList.size(); i++){
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i).get("NAME"));
		}
		
		return dataSourceMap;
    }
	
	public Map getOptTypeMap() {
		Map dataSourceMap = new LinkedMap();

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL("select NAME, VALUE from OMC_DIC_TABLE where DIC_NAME = 'DATA_COLLECT_OPT' ");

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());

		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("NAME"));
		}

		return dataSourceMap;
	}

	public void saveDataCollect(DataCollect dataCollect) {
		
		initDataCollectRegion(dataCollect);
		
		dataCollectDao.save(dataCollect);
	}
	
    public void initDataCollectRegion(DataCollect dataCollect) {
		
        String regionCode = manageRegionDao.getRegionCodeByRegionName(dataCollect.getCity());		
		
		if (regionCode == null) {
			regionCode = "000000";
		}
		
		dataCollect.setRegionCode(regionCode);	
	}
	
	

	public void updateDataCollect(DataCollect dataCollect) {
		dataCollectDao.update(dataCollect);
	}

	public void delete(List deleteList) {
		dataCollectDao.remove(deleteList);
	}

	public ManageRegionDao getManageRegionDao() {
		return manageRegionDao;
	}

	public void setManageRegionDao(ManageRegionDao manageRegionDao) {
		this.manageRegionDao = manageRegionDao;
	}

}